package com.nyist.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.nyist.pojo.User;

/**
 * @author hu
 *
 * 2017年9月22日
 */
public class ExcelToList {
	
	@SuppressWarnings("static-access")
	public static String getValue(XSSFCell XSSFCell) {  
	       if (XSSFCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {  
	  
	           return String.valueOf(XSSFCell.getBooleanCellValue());  
	       } else if (XSSFCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {  
	  
	           return String.valueOf(XSSFCell.getNumericCellValue());  
	       } else {  
	  
	           return String.valueOf(XSSFCell.getStringCellValue());  
	       }  
	   }
	
	@SuppressWarnings("resource")
	public static List<User> readXls() throws IOException {  
	    	
	    InputStream is = new FileInputStream("C:\\Users\\hu\\Desktop\\用户表.xlsx");  
	    
	    XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);  
	    User user = null;
	    List<User> list = new ArrayList<>();  
	
	    for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {  
	        XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);  
	        if (hssfSheet == null) {  
	            continue;  
	        }  
	
	        for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {  
	            XSSFRow hssfRow = hssfSheet.getRow(rowNum);  
	            if (hssfRow != null) {  
	                 user = new User();
	                 XSSFCell id = hssfRow.getCell(0);  
                     XSSFCell username = hssfRow.getCell(1);  
                     XSSFCell password = hssfRow.getCell(2);  
                     XSSFCell address = hssfRow.getCell(3); 
                     XSSFCell zipcode = hssfRow.getCell(4);
                     XSSFCell phone = hssfRow.getCell(5);
                     XSSFCell email = hssfRow.getCell(6);
                     user.setId(getValue(id));
                     user.setUsername(getValue(username));
                     user.setPassword(getValue(password));
                     user.setAddress(getValue(address));
                     user.setZipcode(getValue(zipcode));
                     user.setPhone(getValue(phone));
                     user.setEmail(getValue(email));
                     list.add(user);
	            }  
	        }  
	    }  
	    return list;  
	} 
	
	public static void main(String[] args) throws Exception {
		List<User> user = readXls();
		for (User user2 : user) {
			System.out.println(user2);
		}
	}
}
